﻿using System;
using System.Data;
using log4net;
using net.windward.utils.ado;

namespace ArrowScheduleCore
{
    /// <summary>
    /// All data for a specific event run a single time. There is no update because a run is immutable once it completes.
    /// </summary>
    public class ScheduleRunManager
    {

        // warning - 2 selects below list the order of the columns - make sure they all match
        private static int ordScheduleRunId = -1;
        private static int ordScheduleId;
        private static int ordRunTime;
        private static int ordRunResult;
        private static int ordRunMessage;
        private static int ordReportFilename;
        private static int ordReportEmailTo;

        private static readonly ILog log = LogManager.GetLogger(typeof(ScheduleRunManager));

        private static void GetOrdinals(IDataRecord reader)
        {
            if (ordScheduleRunId != -1)
                return;

            ordScheduleId = reader.GetOrdinal("scheduleId");
            ordRunTime = reader.GetOrdinal("runTime");
            ordRunResult = reader.GetOrdinal("runResult");
            ordRunMessage = reader.GetOrdinal("runMessage");
            ordReportFilename = reader.GetOrdinal("reportFilename");
            ordReportEmailTo = reader.GetOrdinal("reportEmailTo");
            ordScheduleRunId = reader.GetOrdinal("scheduleRunId");
        }

        /// <summary>
        /// Return a specific scheduled run. Will return null if it does not exist.
        /// </summary>
        /// <param name="id">The primary key of the run to return.</param>
        /// <returns>The requested run. null if does not exist.</returns>
        public ScheduleRunItem FindById(long id)
        {

            using (IDbConnection conn = ScheduleManager.CreateConnection())
            {
                conn.Open();
                using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                {
                    try
                    {
                        ScheduleRunItem item;
                        cmd.CommandText = "select * from ScheduleRun where scheduleRunId = ?";
                        cmd.Set(0, id);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            if (!reader.Read())
                            {
                                if (log.IsDebugEnabled)
                                    log.Debug("FindById(" + id + ") = null");
                                item = null;
                            }
                            else
                                item = BuildScheduleRunItemFromResultSet(reader);
                        }
                        if (log.IsDebugEnabled)
                            log.Debug("FindById(" + id + ") successful");
                        return item;
                    }
                    catch (Exception ex)
                    {
                        if (log.IsWarnEnabled)
                            log.Warn("FindById(" + id + ") failed", ex);
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Returns all local scheduled runs. This does not include global runs for the client user.
        /// </summary>
        /// <returns>All local runs for the client user.</returns>
        public ScheduleRunItemCollection FindAll()
        {

            using (IDbConnection conn = ScheduleManager.CreateConnection())
            {
                conn.Open();
                using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                {
                    try
                    {
                        ScheduleRunItemCollection coll = new ScheduleRunItemCollection();
                        cmd.CommandText = "select * from ScheduleRun order by runTime, scheduleId";

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                ScheduleRunItem item = BuildScheduleRunItemFromResultSet(reader);
                                if (item != null)
                                    coll.Add(item);
                            }
                        }
                        if (log.IsDebugEnabled)
                            log.Debug("FindAll() Count = " + coll.Count);
                        return coll;
                    }
                    catch (Exception ex)
                    {
                        log.Error("FindAll() failed", ex);
                        throw;
                    }
                }
            }
        }

        private static ScheduleRunItem BuildScheduleRunItemFromResultSet(IDataRecord reader)
        {
            GetOrdinals(reader);
            return new ScheduleRunItem(reader.GetInt64(ordScheduleRunId),
                                  reader.GetInt64(ordScheduleId),
                                  new DateTime(reader.GetDateTime(ordRunTime).Ticks, DateTimeKind.Utc), // convert to UTC
                                  (ScheduleRunItem.RESULT)reader.GetByte(ordRunResult),
                                  reader.IsDBNull(ordRunMessage) ? null : reader.GetString(ordRunMessage),
                                  reader.IsDBNull(ordReportFilename) ? null : reader.GetString(ordReportFilename),
                                  reader.IsDBNull(ordReportEmailTo) ? null : reader.GetString(ordReportEmailTo)
                                  );
        }

        /// <summary>
        /// Create a new row in the scheduled run table.
        /// </summary>
        /// <param name="item">The values used to populate this row in the table.</param>
        public void Insert(ScheduleRunItem item)
        {

            if (log.IsDebugEnabled)
                log.Debug("Insert: " + item);

            using (IDbConnection conn = ScheduleManager.CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                        {
                            // insert the new ones
                            cmd.Transaction = txn;
                            cmd.CommandText = "insert into ScheduleRun (scheduleId, runTime, runResult, runMessage, reportFilename, reportEmailTo) VALUES (?, ?, ?, ?, ?, ?)";
                            cmd.Set(0, item.ScheduleId);
                            cmd.Set(1, item.RunTime); // UTC
                            cmd.Set(2, (Byte)item.RunResult);
                            cmd.Set(3, item.RunMessage);
                            cmd.Set(4, item.ReportFilename);
                            cmd.Set(5, item.ReportEmailTo);

                            long id = cmd.ExecuteInsert("scheduleRunId");
                            item.setId(id);
                            if (log.IsDebugEnabled)
                                log.Debug("Insert = " + id);
                        }

                        txn.Commit();
                    }
                    catch (Exception ex)
                    {
                        txn.Rollback();
                        log.Error("Insert(" + item.Id + ") failed", ex);
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// Delete this run from the table.
        /// </summary>
        /// <param name="id">The primary key to delete.</param>
        /// <returns>The number of rows deleted. Should always be 1 (deleted) or 0(doesn't exist).</returns>
        public int Delete(long id)
        {

            if (log.IsDebugEnabled)
                log.Debug("Delete: " + id);

            using (IDbConnection conn = ScheduleManager.CreateConnection())
            {
                conn.Open();
                using (IDbTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    using (WrCommand cmd = (WrCommand)conn.CreateCommand())
                    {
                        try
                        {
                            cmd.Transaction = txn;
                            cmd.CommandText = "delete from ScheduleRun where scheduleRunId = ?";
                            cmd.Set(0, id);

                            int num = cmd.ExecuteNonQuery();

                            txn.Commit();

                            if (log.IsDebugEnabled)
                                log.Debug("Delete(" + id + ") = " + num);

                            return num;
                        }
                        catch (Exception ex)
                        {
                            txn.Rollback();
                            log.Error("Delete(" + id + ") failed", ex);
                            throw;
                        }
                    }
                }
            }
        }
    }
}
